package com.scuec.trainingmanagementsys.mapper;

import com.scuec.trainingmanagementsys.entity.Project;
import com.scuec.trainingmanagementsys.entity.TeamMember;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
@Mapper
public interface TeamMemberMapper {

    /**
     * 插入团队成员信息
     * @param teamMember 团队成员对象
     * @return 插入成功的记录数
     */
    @Insert("INSERT INTO teammember (TeamMemberID, TeamID, StudentID, MemberRole) VALUES (#{teamMemberID}, #{teamID}, #{studentID}, #{memberRole})")
    int insertTeamMember(TeamMember teamMember);

    /**
     * 将指定团队成员的角色更新为 Leader
     * @param teamMemberID 团队成员 ID
     * @return 更新成功的记录数
     */
    @Update("UPDATE teammember SET MemberRole = 'Leader' WHERE TeamMemberID = #{teamMemberID} AND MemberRole = 'Member'")
    int updateMemberRoleToLeader(Integer teamMemberID);

    /**
     * 查询学生是否已经加入某个项目
     * @param studentId 学生 ID
     * @param projectId 项目 ID
     * @return 加入记录数
     */
    @Select("SELECT COUNT(*) FROM teammember tm " +
            "JOIN team t ON tm.TeamID = t.TeamID " +
            "WHERE tm.StudentID = #{studentId} AND t.ProjectID = #{projectId}")
    int isStudentJoinedProject(@Param("studentId") Integer studentId, @Param("projectId") Integer projectId);

    /**
     * 根据项目ID获取团队成员详情
     * @param projectId 项目ID
     * @return 团队成员详情列表
     */
    @Select("SELECT tm.TeamMemberID, tm.TeamID, tm.StudentID, tm.MemberRole, t.teamName, s.studentName " +
            "FROM teammember tm " +
            "JOIN team t ON tm.TeamID = t.TeamID " +
            "JOIN student s ON tm.StudentID = s.studentID " +
            "JOIN project p ON t.ProjectID = p.ProjectID " +
            "WHERE p.ProjectID = #{projectId}")
    List<TeamMember> getTeamMembersByProjectId(@Param("projectId") Integer projectId);

    /**
     * 根据学生ID获取学生加入的项目列表
     * @param studentId 学生ID
     * @return 项目列表
     */
    @Select("SELECT p.*, tm.MemberRole " +
            "FROM project p " +
            "JOIN team t ON p.ProjectID = t.ProjectID " +
            "JOIN teammember tm ON t.TeamID = tm.TeamID " +
            "WHERE tm.StudentID = #{studentId}")
    List<Project> getJoinedProjects(@Param("studentId") Integer studentId);

    /**
     * 根据学生ID获取学生参与的项目列表
     * @param studentId 学生ID
     * @return 项目列表
     */
    @Select("SELECT p.* FROM project p " +
            "JOIN team t ON p.ProjectID = t.ProjectID " +
            "JOIN teammember tm ON t.TeamID = tm.TeamID " +
            "WHERE tm.StudentID = #{studentId}")
    List<Project> getProjectsByStudentId(@Param("studentId") Integer studentId);

    /**
     * 根据学生ID获取学生参与的项目列表（包含指导老师姓名）
     * @param studentId 学生ID
     * @return 包含指导老师姓名的项目列表
     */
    @Select("SELECT p.*, te.TeacherName " +
            "FROM project p " +
            "JOIN team t ON p.ProjectID = t.ProjectID " +
            "JOIN teammember tm ON t.TeamID = tm.TeamID " +
            "JOIN teacher te ON p.TeacherID = te.TeacherID " +
            "WHERE tm.StudentID = #{studentId}")
    List<Project> getProjectsWithTeacherByStudentId(@Param("studentId") Integer studentId);

    /**
     * 获取学生按项目的分布数据
     * @return 包含项目名称和学生数量的列表
     */
    @Select("SELECT p.projectName, COUNT(tm.StudentID) AS studentCount " +
            "FROM project p " +
            "JOIN team t ON p.ProjectID = t.ProjectID " +
            "JOIN teammember tm ON t.TeamID = tm.TeamID " +
            "GROUP BY p.projectName")
    List<Map<String, Object>> getStudentDistributionByProject();

    /**
     * 根据项目ID获取团队成员详情，包括学生姓名和成员角色
     * @param projectId 项目ID
     * @return 团队成员详情列表
     */
    @Select("SELECT tm.TeamMemberID, tm.TeamID, tm.StudentID, tm.MemberRole, s.studentName " +
            "FROM teammember tm " +
            "JOIN student s ON tm.StudentID = s.studentID " +
            "JOIN team t ON tm.TeamID = t.TeamID " +
            "JOIN project p ON t.ProjectID = p.ProjectID " +
            "WHERE p.ProjectID = #{projectId}")
    List<TeamMember> getTeamMembersDetailsByProjectId(@Param("projectId") Integer projectId);

    /**
     * 更新团队成员的角色
     * @param teamMemberID 团队成员ID
     * @param memberRole 新的成员角色
     * @return 更新成功的记录数
     */
    @Update("UPDATE teammember SET MemberRole = #{memberRole} WHERE TeamMemberID = #{teamMemberID}")
    int updateMemberRole(@Param("teamMemberID") Integer teamMemberID, @Param("memberRole") String memberRole);

    /**
     * 根据学生ID获取学生姓名
     * @param studentId 学生ID
     * @return 学生姓名
     */
    @Select("SELECT studentName FROM student WHERE studentID = #{studentId}")
    String getStudentNameByStudentId(@Param("studentId") Integer studentId);

    // TeamMemberMapper.java
    @Select("SELECT tm.*, s.studentName " +
            "FROM teammember tm " +
            "JOIN student s ON tm.StudentID = s.studentID " +
            "WHERE tm.TeamID = #{teamId}")
    List<TeamMember> getTeamMembersByTeamId(@Param("teamId") Integer teamId);

    /**
     * 根据学生 ID 获取 TeamMember
     * @param studentId 学生 ID
     * @return TeamMember 对象
     */
    @Select("SELECT * FROM teammember WHERE StudentID = #{studentId} LIMIT 1")
    TeamMember getTeamMemberByStudentId(@Param("studentId") Integer studentId);

    /**
     * 根据 TeamMemberID 获取 TeamMember
     * @param teamMemberID TeamMember ID
     * @return TeamMember 对象
     */
    @Select("SELECT * FROM teammember WHERE TeamMemberID = #{teamMemberID}")
    TeamMember getTeamMemberById(@Param("teamMemberID") Integer teamMemberID);
    // 添加@Param注解
    /**
     * 根据学生ID和项目ID获取团队ID
     */
    @Select("SELECT tm.TeamID FROM teammember tm " +
            "JOIN team t ON tm.TeamID = t.TeamID " +
            "WHERE tm.StudentID = #{studentId} AND t.ProjectID = #{projectId}")
    Integer getTeamIdByStudentAndProject(@Param("studentId") Integer studentId,
                                         @Param("projectId") Integer projectId);

    @Select("SELECT memberRole FROM teamMember WHERE teamID = #{teamID} AND studentID = #{studentID}")
    String getMemberRole(Integer teamID, Integer studentID);
    @Select("SELECT * FROM teammember WHERE teamID = #{teamID} AND studentID = #{studentID}")
    TeamMember getTeamMemberByTeamIdAndStudentId(@Param("teamID") Integer teamID,
                                                 @Param("studentID") Integer studentID);

}